from PDBC import msq_connecter
import mysql.connector

def connect_to_database():
    db = mysql.connector.connect(
        host="cd-cdb-lfpafgh4.sql.tencentcdb.com",  # 数据库主机地址
        user="gameadd",  # 数据库用户名
        password="123456qwer",  # 数据库密码
        database="2048_game" , # 数据库名
        port=20077,
    )
    return db
def search_user(username):#查
    db = connect_to_database()
    cursor = db.cursor()

    # 执行SQL查询
    cursor.execute("SELECT * FROM user_information WHERE user_id = %s", (username,))

    # 获取所有记录列表
    results = cursor.fetchall()
    row = cursor.rowcount
    for row in results:
        print(row)
    return row,results
    # 关闭数据库连接
    db.close()

def add_user(username,password,phonenumber):#增
    db = connect_to_database()
    cursor = db.cursor()
    sql = "INSERT INTO user_information (user_id, user_password,user_phonenumber) VALUES (%s, %s, %s)"
    val = (username, password,phonenumber)
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, "记录插入成功。")
    db.close()


def search_goallist():#查
    db = connect_to_database()
    cursor = db.cursor()

    # 执行SQL查询
    cursor.execute("SELECT * FROM score_list")

    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        print(row)
    return row,results
    # 关闭数据库连接
    db.close()

def add_goallist(name,score):#增
    db = connect_to_database()
    cursor = db.cursor()
    sql = "INSERT INTO score_list (name, score) VALUES (%s, %s)"
    val = (name, score)
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, "记录插入成功。")
    db.close()
def delete_goallist(name):#删
    db = connect_to_database()
    cursor = db.cursor()
    sql = "DELETE FROM score_list WHERE name = %s"
    val = (name,)
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, " 条记录删除")
    db.close()


## 以下为测试代码
def main():
    db = connect_to_database()
    cursor = db.cursor()

    # 执行SQL查询
    cursor.execute("SELECT * FROM user_information")

    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        print(row)

    # 关闭数据库连接
    db.close()

if __name__ == "__main__":
    main()